{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "# 调用model\n",
    "# django 快速入门 数据操作 https://blog.csdn.net/u011054333/article/details/78767724\n",
    "\n",
    "# 独立使用django model（二）\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "\n",
    "\n",
    "# 建立外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project,'rmis'))\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# sys.path.append已设置临时环境变量，可以找到rmis.settings，脚本是外部脚本，只是放在了项目当中而已\n",
    "# 相关数据库配置等在settings.py文件中\n",
    "\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'django.db.models.query.QuerySet'>\n",
      "code               object\n",
      "created    datetime64[ns]\n",
      "title              object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 脚本正文\n",
    "from good.models import Good\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# model  >dataframe\n",
    "queryset = Good.objects.all()[:3].values('code','title','created')\n",
    "data = list(queryset)\n",
    "'''\n",
    "list(Good.objects.all()[:3].values('code','title','created'))\n",
    "[\n",
    "    {'code': '2019031949511', 'title': 'TestGoodA'},\n",
    "    {'code': '2019022585211', 'title': '女装 (UT)SPRZ NY印花T恤(短袖) 417649'},\n",
    "    {'code': '2019022554538', 'title': '女装 (UT)SPRZ NY印花T恤(短袖) 417651'},\n",
    "]\n",
    "'''\n",
    "df = pd.DataFrame(data)\n",
    "print(type(queryset))\n",
    "# django-pandas queryset > dataframe\n",
    "# https://github.com/chrisdev/django-pandas\n",
    "\n",
    "# pandas.DataFrame.from_dict\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.from_dict.html#pandas.DataFrame.from_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "code                      object\n",
      "collar                    object\n",
      "created           datetime64[ns]\n",
      "dye                       object\n",
      "fk_store_id               object\n",
      "graphy                    object\n",
      "id                         int64\n",
      "info                      object\n",
      "length                    object\n",
      "material                  object\n",
      "modified          datetime64[ns]\n",
      "price_purchase             int64\n",
      "price_sell                 int64\n",
      "season                    object\n",
      "size                      object\n",
      "sleeve                    object\n",
      "style                     object\n",
      "tailer                    object\n",
      "title                     object\n",
      "year                      object\n",
      "dtype: object\n",
      "------------------\n",
      "code                      object\n",
      "collar                    object\n",
      "created           datetime64[ns]\n",
      "dye                       object\n",
      "fk_store_id               object\n",
      "graphy                    object\n",
      "id                         int64\n",
      "info                      object\n",
      "length                    object\n",
      "material                  object\n",
      "modified          datetime64[ns]\n",
      "price_purchase             int64\n",
      "price_sell                 int64\n",
      "season                    object\n",
      "size                      object\n",
      "sleeve                    object\n",
      "style                     object\n",
      "tailer                    object\n",
      "title                     object\n",
      "year                      object\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(list(Good.objects.all()[:3].values()))\n",
    "print(df.dtypes)\n",
    "print('------------------')\n",
    "df = pd.DataFrame(list(Good.objects.all()[:3].values()))\n",
    "print(df.dtypes)\n",
    "\n",
    "# 像Excel一样使用python进行数据分析\n",
    "# http://www.cnblogs.com/nxld/p/6756492.html\n",
    "\n",
    "# 十分钟快速入门 Pandas\n",
    "# http://codingpy.com/article/a-quick-intro-to-pandas/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'code': ['2019031949511', '2019022585211', '2019022554538'],\n",
       " 'collar': [None, None, None],\n",
       " 'created': [Timestamp('2019-03-19 14:35:44.249749'),\n",
       "  Timestamp('2019-03-16 10:21:37.461916'),\n",
       "  Timestamp('2019-03-16 10:21:37.016238')],\n",
       " 'dye': [None, None, None],\n",
       " 'fk_store_id': [None, None, None],\n",
       " 'graphy': [None, None, None],\n",
       " 'id': [87, 86, 85],\n",
       " 'info': ['', '', ''],\n",
       " 'length': [None, None, None],\n",
       " 'material': [None, None, None],\n",
       " 'modified': [Timestamp('2019-03-19 16:33:46.425616'),\n",
       "  Timestamp('2019-03-16 10:21:37.461944'),\n",
       "  Timestamp('2019-03-16 10:21:37.016264')],\n",
       " 'price_purchase': [100, 49, 49],\n",
       " 'price_sell': [200, 99, 99],\n",
       " 'season': [None, None, None],\n",
       " 'size': ['fz', 'fz', 'fz'],\n",
       " 'sleeve': [None, None, None],\n",
       " 'style': [None, None, None],\n",
       " 'tailer': [None, None, None],\n",
       " 'title': ['TestGoodA',\n",
       "  '女装 (UT)SPRZ NY印花T恤(短袖) 417649',\n",
       "  '女装 (UT)SPRZ NY印花T恤(短袖) 417651'],\n",
       " 'year': [None, None, None]}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict(orient='list')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'title': {0: 'TestGoodB'},\n",
       " 'year': {0: 2019},\n",
       " 'code': {0: 2019030123451},\n",
       " 'price_sell': {0: 200},\n",
       " 'price_purchase': {0: 100},\n",
       " 'modified': {0: Timestamp('2018-08-09 10:55:00')}}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict()  # Default DataFrame.to_dict(orient=’dict’)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'index': [0],\n",
       " 'columns': ['title',\n",
       "  'year',\n",
       "  'code',\n",
       "  'price_sell',\n",
       "  'price_purchase',\n",
       "  'modified'],\n",
       " 'data': [['TestGoodB',\n",
       "   2019,\n",
       "   2019030123451,\n",
       "   200,\n",
       "   100,\n",
       "   Timestamp('2018-08-09 10:55:00')]]}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict(orient='split')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: {'title': 'TestGoodB',\n",
       "  'year': 2019,\n",
       "  'code': 2019030123451,\n",
       "  'price_sell': 200,\n",
       "  'price_purchase': 100,\n",
       "  'modified': Timestamp('2018-08-09 10:55:00')}}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict(orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'title': 'TestGoodB',\n",
       "  'year': 2019,\n",
       "  'code': 2019030123451,\n",
       "  'price_sell': 200,\n",
       "  'price_purchase': 100,\n",
       "  'modified': Timestamp('2018-08-09 10:55:00')}]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <td>TestGoodB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <td>2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</th>\n",
       "      <td>2019030123451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price_sell</th>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>price_purchase</th>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>modified</th>\n",
       "      <td>2018-08-09 10:55:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  0\n",
       "title                     TestGoodB\n",
       "year                           2019\n",
       "code                  2019030123451\n",
       "price_sell                      200\n",
       "price_purchase                  100\n",
       "modified        2018-08-09 10:55:00"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.T  # 转置"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pandas.DataFrame.to_dict\n",
    "# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_dict.html?highlight=to_dict#pandas.DataFrame.to_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<bound method NDFrame.to_json of             code collar                    created   dye fk_store_id graphy  \\\n",
      "0  2019031949511   None 2019-03-19 14:35:44.249749  None        None   None   \n",
      "1  2019022585211   None 2019-03-16 10:21:37.461916  None        None   None   \n",
      "2  2019022554538   None 2019-03-16 10:21:37.016238  None        None   None   \n",
      "\n",
      "   id info length material                   modified  price_purchase  \\\n",
      "0  87        None     None 2019-03-19 16:33:46.425616             100   \n",
      "1  86        None     None 2019-03-16 10:21:37.461944              49   \n",
      "2  85        None     None 2019-03-16 10:21:37.016264              49   \n",
      "\n",
      "   price_sell season size sleeve style tailer                          title  \\\n",
      "0         200   None   fz   None  None   None                      TestGoodA   \n",
      "1          99   None   fz   None  None   None  女装 (UT)SPRZ NY印花T恤(短袖) 417649   \n",
      "2          99   None   fz   None  None   None  女装 (UT)SPRZ NY印花T恤(短袖) 417651   \n",
      "\n",
      "   year  \n",
      "0  None  \n",
      "1  None  \n",
      "2  None  >\n",
      "<bound method NDFrame.to_excel of             code collar                    created   dye fk_store_id graphy  \\\n",
      "0  2019031949511   None 2019-03-19 14:35:44.249749  None        None   None   \n",
      "1  2019022585211   None 2019-03-16 10:21:37.461916  None        None   None   \n",
      "2  2019022554538   None 2019-03-16 10:21:37.016238  None        None   None   \n",
      "\n",
      "   id info length material                   modified  price_purchase  \\\n",
      "0  87        None     None 2019-03-19 16:33:46.425616             100   \n",
      "1  86        None     None 2019-03-16 10:21:37.461944              49   \n",
      "2  85        None     None 2019-03-16 10:21:37.016264              49   \n",
      "\n",
      "   price_sell season size sleeve style tailer                          title  \\\n",
      "0         200   None   fz   None  None   None                      TestGoodA   \n",
      "1          99   None   fz   None  None   None  女装 (UT)SPRZ NY印花T恤(短袖) 417649   \n",
      "2          99   None   fz   None  None   None  女装 (UT)SPRZ NY印花T恤(短袖) 417651   \n",
      "\n",
      "   year  \n",
      "0  None  \n",
      "1  None  \n",
      "2  None  >\n"
     ]
    }
   ],
   "source": [
    "print(df.to_json)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{0: {'code': '2019031949511',\n",
       "  'collar': None,\n",
       "  'created': Timestamp('2019-03-19 14:35:44.249749'),\n",
       "  'dye': None,\n",
       "  'fk_store_id': None,\n",
       "  'graphy': None,\n",
       "  'id': 87,\n",
       "  'info': '',\n",
       "  'length': None,\n",
       "  'material': None,\n",
       "  'modified': Timestamp('2019-03-19 16:33:46.425616'),\n",
       "  'price_purchase': 100,\n",
       "  'price_sell': 200,\n",
       "  'season': None,\n",
       "  'size': 'fz',\n",
       "  'sleeve': None,\n",
       "  'style': None,\n",
       "  'tailer': None,\n",
       "  'title': 'TestGoodA',\n",
       "  'year': None},\n",
       " 1: {'code': '2019022585211',\n",
       "  'collar': None,\n",
       "  'created': Timestamp('2019-03-16 10:21:37.461916'),\n",
       "  'dye': None,\n",
       "  'fk_store_id': None,\n",
       "  'graphy': None,\n",
       "  'id': 86,\n",
       "  'info': '',\n",
       "  'length': None,\n",
       "  'material': None,\n",
       "  'modified': Timestamp('2019-03-16 10:21:37.461944'),\n",
       "  'price_purchase': 49,\n",
       "  'price_sell': 99,\n",
       "  'season': None,\n",
       "  'size': 'fz',\n",
       "  'sleeve': None,\n",
       "  'style': None,\n",
       "  'tailer': None,\n",
       "  'title': '女装 (UT)SPRZ NY印花T恤(短袖) 417649',\n",
       "  'year': None},\n",
       " 2: {'code': '2019022554538',\n",
       "  'collar': None,\n",
       "  'created': Timestamp('2019-03-16 10:21:37.016238'),\n",
       "  'dye': None,\n",
       "  'fk_store_id': None,\n",
       "  'graphy': None,\n",
       "  'id': 85,\n",
       "  'info': '',\n",
       "  'length': None,\n",
       "  'material': None,\n",
       "  'modified': Timestamp('2019-03-16 10:21:37.016264'),\n",
       "  'price_purchase': 49,\n",
       "  'price_sell': 99,\n",
       "  'season': None,\n",
       "  'size': 'fz',\n",
       "  'sleeve': None,\n",
       "  'style': None,\n",
       "  'tailer': None,\n",
       "  'title': '女装 (UT)SPRZ NY印花T恤(短袖) 417651',\n",
       "  'year': None}}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.T.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['title', 'year', 'code', 'price_sell', 'price_purchase', 'modified']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "list(\n",
    "    {'title': 'TestGoodB',\n",
    "  'year': 2019,\n",
    "  'code': 2019030123451,\n",
    "  'price_sell': 200,\n",
    "  'price_purchase': 100,\n",
    "  'modified': pd.Timestamp('2018-08-09 10:55:00')}\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "交集： {2, 3}\n",
      "并集： {1, 2, 3, 4, 6, 7, 8}\n",
      "s1独有： {1, 4}\n",
      "s2独有： {8, 6, 7}\n",
      "s1是否是s2子集： False\n",
      "s2是否是s1子集： False\n"
     ]
    }
   ],
   "source": [
    "s1 = set([1,2,3,4])\n",
    "s2 = set([2,3,6,7,8])\n",
    "print('交集：',s1.intersection(s2))\n",
    "print('并集：',s1.union(s2))\n",
    "print('s1独有：',s1.difference(s2))\n",
    "print('s2独有：',s2.difference(s1))\n",
    "print('s1是否是s2子集：',s1.issubset(s2))\n",
    "print('s2是否是s1子集：',s2.issubset(s1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "共有： [1, 1]\n"
     ]
    }
   ],
   "source": [
    "l1 = [1,1,2,2]\n",
    "l2 = [1,1,3]\n",
    "print('共有：', [i for i in l1 if i in l2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
